-- 以下函数批量更新用户标示符
CREATE OR REPLACE FUNCTION revision_data () 
  RETURNS   int4 AS 
$BODY$ 
DECLARE 
  r   RECORD ; 
  del   bool ; 
  num   int4 := 0 ; 
  sql   "varchar" ; 
  idval int8 := 100000525;--起始标识符
BEGIN 
  sql := 'select * from member'; 
  FOR   r IN EXECUTE sql LOOP 
    IF   length(r . identifier) <> 9 THEN 
        update member set  identifier = '' || idval || '' where identifier = r . identifier ; 
        update member_audit set identifier = '' || idval || '' where identifier = r . identifier ;
        update member_contact set member = '' || idval || '' where member = r . identifier ;
        update member_detail set member = '' || idval || '' where member = r . identifier ;
        update member_learning set member = '' || idval || '' where member = r . identifier ;
        update member_out_account set member = '' || idval || '' where member = r . identifier ;
        update member_personality set member = '' || idval || '' where member = r . identifier ;
        update member_work set member = '' || idval || '' where member = r . identifier ;
        update bonus_apply set member = '' || idval || '' where member = r . identifier ;
        update comment set member = '' || idval || '' where member = r . identifier ;
        update comment set member = '' || idval || '' where member = r . identifier ;
        update doing set member = '' || idval || '' where member = r . identifier ;
        update doing set tomember = '' || idval || '' where tomember = r . identifier ;
        update doing_follow set member = '' || idval || '' where member = r . identifier ;
        update doing_love set create_member = '' || idval || '' where create_member = r . identifier ;
        update doing_love set receive_member = '' || idval || '' where receive_member = r . identifier ;
        update doing_pictorial set member = '' || idval || '' where member = r . identifier ;
        update doing_pictorial_visit set member = '' || idval || '' where member = r . identifier ;
        update doing_picture set member = '' || idval || '' where member = r . identifier ;
        update doing_product set member = '' || idval || '' where member = r . identifier ;
        update doing_sync set member = '' || idval || '' where member = r . identifier ;
        update doing_topic set member = '' || idval || '' where member = r . identifier ;
        update forum set creator = '' || idval || '' where creator = r . identifier ;
        update forum_member set member = '' || idval || '' where member = r . identifier ;
        update mention set member = '' || idval || '' where member = r . identifier ;
        update message set send_member = '' || idval || '' where send_member = r . identifier ;
        update "order" set member = '' || idval || '' where member = r . identifier ;
        update product set member = '' || idval || '' where member = r . identifier ;
        update rush_phone set member = '' || idval || '' where member = r . identifier ;
        idval := idval +1;
        num := num + 1 ;
    END   IF ; 
  END   LOOP ; 
  return   num ; 
END ; 
$BODY$ 
LANGUAGE   'plpgsql' VOLATILE ;
-- 以上函数批量更新用户标示符

-- 以下函数批量更新人民币账户所关联的家标识符为家中户主（会员）的标识符
CREATE OR REPLACE FUNCTION revision_data () 
  RETURNS   int4 AS 
$BODY$ 
DECLARE 
  r   RECORD ; 
  num   int4 := 0 ; 
  flag  int4 := 0;
  sql   "varchar" ;
BEGIN 
  sql := 'select family from account'; 
  FOR   r IN EXECUTE sql LOOP
    select count(*) into flag from member where family=r.family;
    IF flag > 1 THEN
      update account set family=(select identifier from member where family=r.family and is_host=true);
    END IF;
    IF flag <= 1 THEN
      update account set family=(select identifier from member where family=r.family);
    END IF;
    num := num + 1 ;
  END   LOOP ; 
  return   num ; 
END ; 
$BODY$ 
LANGUAGE   'plpgsql' VOLATILE ;
-- 以下函数批量更新人民币账户所关联的家标识符为家中户主（会员）的标识符

update cms_content set picture=replace(picture, '192.168.0.109', 'img.guoguojie.com');

--删除商品
delete from cms_content where tbl_name='doing' and tbl_identifier in (select doing from doing_product where "from" = '淘宝' and identifier=original_id group by doing);
delete from cms_content where tbl_name='doing_product' and tbl_identifier in (select identifier from doing_product where "from" = '淘宝' and identifier=original_id);
delete from "comment" where comment_id_type='doingid' and comment_id in (select doing from doing_product where "from" = '淘宝' and identifier=original_id group by doing);
delete from pictorial_doing where doing in (select doing from doing_product where "from" = '淘宝' and identifier=original_id group by doing);
delete from doing_topic_relation where doing in (select doing from doing_product where "from" = '淘宝' and identifier=original_id group by doing);
delete from doing_system_tag where doing in (select doing from doing_product where "from" = '淘宝' and identifier=original_id group by doing);
delete from doing_stat where doing in (select doing from doing_product where "from" = '淘宝' and identifier=original_id group by doing);
delete from doing_love where doing in (select doing from doing_product where "from" = '淘宝' and identifier=original_id group by doing);
delete from doing_picture where doing in (select doing from doing_product where "from" = '淘宝' and identifier=original_id group by doing);
delete from doing where identifier in (select doing from doing_product where "from" = '淘宝' and identifier=original_id group by doing);
delete from doing_product where "from" = '淘宝' and identifier=original_id;